package vcp.server.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;

import vcp.entity.DayStatistics;
import vcp.enums.SiteEnum;
import vcp.server.DBAdapter;
import vcp.server.DAO.interfaces.IGenericDAO;
import vcp.server.utility.Constants.DayStatisticsTable;
import vcp.server.utility.Constants.SubscribtionsTable;
import vcp.server.utility.Constants.SubscriptionHistoryTable;
import vcp.server.utility.Constants.TableNames;
import vcp.server.utility.SQLQueries;

public class DayStatisticsDAO implements IGenericDAO <DayStatistics, Void>
{
	// ****************************************************************************************************************************
	// Public methods
	// ****************************************************************************************************************************
	public List <DayStatistics> findAllFromSiteByTimePeriod ( DateTime startDate, DateTime endDate, SiteEnum siteEnum )
	{
		List <DayStatistics> dayStatisticsList = new ArrayList <>();
		String query =
				SQLQueries.SELECT_ALL
						+ SQLQueries.FROM + TableNames.DAY_STATISTICS
						+ " " + SQLQueries.WHERE
						+ SubscriptionHistoryTable.SITE_NAME + "=" + "\"" + siteEnum.toString() + "\""
						+ " " + SQLQueries.AND
						+ " " + SQLQueries.DATE + "(" + DayStatisticsTable.DATE + ")"
						+ SQLQueries.GTE
						+ " " + "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(startDate) + "\""
						+ " " + SQLQueries.AND
						+ SQLQueries.DATE + "(" + DayStatisticsTable.DATE + ")"
						+ SQLQueries.LTE
						+ "\"" + DateTimeFormat.forPattern("yyyy-MM-dd").print(endDate) + "\"";

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			while ( set.next() )
			{
				DayStatistics dayStatistics = this.create();

				dayStatistics.setID(set.getInt(DayStatisticsTable.ID));
				dayStatistics.setNumOfUsedOrders(set.getInt(DayStatisticsTable.NUM_OF_USED_ORDERS));
				dayStatistics.setNumOfCanceledOrders(set.getInt(DayStatisticsTable.NUM_OF_CANCEL_ORDERS));
				dayStatistics.setNumOfSubscription(set.getInt(DayStatisticsTable.NUM_OF_SUBSCRIPTIONS));
				dayStatistics.setNumOfBussinessClass(set.getInt(DayStatisticsTable.NUM_OF_BUSSINESS_CLASS));
				dayStatistics.setNumOfParkingLates(set.getInt(DayStatisticsTable.NUM_OF_PARKING_LATE));
				dayStatistics.setDate(new DateTime(set.getTimestamp(DayStatisticsTable.DATE)));
				dayStatistics.setSiteEnum(SiteEnum.convert(set.getString(DayStatisticsTable.SITE_NAME)));

				dayStatisticsList.add(dayStatistics);
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return dayStatisticsList;
	}

	// ****************************************************************************************************************************
	// IGenericDAO methods
	// ****************************************************************************************************************************
	@Override
	public Integer findMaxID ()
	{
		Integer dayStatisticsID = null;
		String query =
				String.format(
					SQLQueries.SELECT_MAX__VALUE__AS__NAME__FROM__TABLE_NAME__
					, DayStatisticsTable.ID
					, SQLQueries.MAX_ID
					, TableNames.DAY_STATISTICS);

		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				dayStatisticsID = set.getInt("maxID");
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		if ( dayStatisticsID == null )
		{
			dayStatisticsID = 0;
		}

		return ++ dayStatisticsID;
	}

	@Override
	public DayStatistics create ()
	{
		return new DayStatistics();
	}

	@Override
	public DayStatistics findByEntity ( Void entity )
	{
		return null;
	}

	@Override
	public DayStatistics findByID ( int ID )
	{
		return this.findByID(String.valueOf(ID));
	}

	@Override
	public DayStatistics findByID ( String ID )
	{
		String query =
				String.format(
					SQLQueries.SELECT_ALL_FROM__SINGLETABLE__WHERE__COMPARE_
					, TableNames.DAY_STATISTICS
					, SubscribtionsTable.ID + "=" + ID);

		DayStatistics dayStatistics = null;
		try
		{
			ResultSet set = DBAdapter.getAdapter().find(query);
			if ( set.next() )
			{
				dayStatistics = this.create();

				dayStatistics.setID(set.getInt(DayStatisticsTable.ID));
				dayStatistics.setNumOfUsedOrders(set.getInt(DayStatisticsTable.NUM_OF_USED_ORDERS));
				dayStatistics.setNumOfCanceledOrders(set.getInt(DayStatisticsTable.NUM_OF_CANCEL_ORDERS));
				dayStatistics.setNumOfSubscription(set.getInt(DayStatisticsTable.NUM_OF_SUBSCRIPTIONS));
				dayStatistics.setNumOfBussinessClass(set.getInt(DayStatisticsTable.NUM_OF_BUSSINESS_CLASS));
				dayStatistics.setNumOfParkingLates(set.getInt(DayStatisticsTable.NUM_OF_PARKING_LATE));
				dayStatistics.setDate(new DateTime(set.getTimestamp(DayStatisticsTable.DATE)));
				dayStatistics.setSiteEnum(SiteEnum.convert(set.getString(DayStatisticsTable.SITE_NAME)));
			}
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return dayStatistics;
	}

	@Override
	public List <DayStatistics> findAll ( Void info )
	{
		return null;
	}

	@Override
	public boolean save ( DayStatistics entity )
	{
		boolean result = false;

		String valuesSet =
				String.format(
					"%d,%d,%d,%d,%d,%d,\"%s\",\"%s\"",
					entity.getID()
					, entity.getNumOfUsedOrders()
					, entity.getNumOfCanceledOrders()
					, entity.getNumOfSubscription()
					, entity.getNumOfBussinessClass()
					, entity.getNumOfParkingLates()
					, entity.getDate() != null
												? new Timestamp(entity.getDate().getMillis())
												: new Timestamp(new DateTime(2001, 1, 1, 0, 0, 0).getMillis())
					, entity.getSiteEnum().toString());

		String sqlQuery =
				String.format(
					SQLQueries.INSERT_INTO__TABLENAME__VALUES__
					, TableNames.DAY_STATISTICS
					, valuesSet);

		try
		{
			result = DBAdapter.getAdapter().save(sqlQuery);
		}
		catch ( SQLException e )
		{
			e.printStackTrace();
		}

		return result;
	}

	@Override
	public boolean refresh ( String sqlQuery )
	{
		return false;
	}

	@Override
	public boolean deleteByID ( int ID )
	{
		return false;
	}

	@Override
	public boolean deleteByID ( String ID )
	{
		return false;
	}

}
